package com.zzzzzz.content.content;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.zzzzzz.common.GlobalConstants;
import com.zzzzzz.core.dao.BaseDao;

@Repository
public class ContentDao extends BaseDao {
	
	@Resource
	private JdbcTemplate jdbcTemplate;
	
	private static final String EDIT_REPLIES = 							"update topic set replies = replies + ? where id = ?";
	private final static String EDIT_LIKES = 							"update topic set likes = likes + ? where id = ?";
	
	private static final String FIND_LISTVIEW = 						"select t.id, t.title, t.text_s, t.review, t.pic_id, t.tags, t.user_id, t.add_dt, t.upd_dt, t.status, t.type_id, t.looks, t.replies, t.likes, u.username, ifnull(u.website, u.id) as website from topic t inner join user u on t.user_id = u.id where t.status = ?";
	private static final String FIND_LISTVIEW_COUNT = 	 				"select count(1) as count from topic t inner join user u on t.user_id = u.id where t.status = ?";
	
	private static final String FIND_BY_TAGID = 						"select t.id, t.title, t.text_s, t.review, t.pic_id, t.tags, t.user_id, t.add_dt, t.upd_dt, t.status, t.type_id, t.looks, t.replies, t.likes, u.username, ifnull(u.website, u.id) as website from topic t inner join topic_tag tt on t.id = tt.topic_id inner join user u on t.user_id = u.id where tt.tag_id = ? and t.status = ?";
	private static final String FIND_BY_TAGID_COUNT = 					"select count(1) as count from topic t inner join topic_tag tt on t.id = tt.topic_id inner join user u on t.user_id = u.id where tt.tag_id = ? and t.status = ?";
	
	public void editReplies(Long id, Integer repliesPlus){
		jdbcTemplate.update(EDIT_REPLIES, repliesPlus, id);
	}

	public void editLikes(Long id, Integer likesPlus){
		jdbcTemplate.update(EDIT_LIKES, likesPlus, id);
	}
	
	protected class ContentListViewRowMapper implements RowMapper<ContentListView> {
		public ContentListView mapRow(ResultSet rs, int rowNum) throws SQLException {
			ContentListView contentListView = new ContentListView();
			contentListView.setId(rs.getLong("id"));
			contentListView.setTitle(rs.getString("title"));
			contentListView.setTextS(rs.getString("text_s"));
			contentListView.setReview(rs.getString("review"));
			contentListView.setPicId(rs.getString("pic_id"));
			contentListView.setUserId(rs.getLong("user_id"));
			contentListView.setTags(rs.getString("tags"));
			contentListView.setAddDt(rs.getTimestamp("add_dt"));
			contentListView.setUpdDt(rs.getTimestamp("upd_dt"));
			contentListView.setTypeId(rs.getInt("type_id"));
			contentListView.setLooks(rs.getInt("looks"));
			contentListView.setLikes(rs.getInt("likes"));
			contentListView.setReplies(rs.getInt("replies"));
			contentListView.setUsername(rs.getString("username"));
			contentListView.setWebsite(rs.getString("website"));
			return contentListView;
		}
	}
	
	public List<ContentListView> find(String orderByField, int start, int offset){
		StringBuilder sql = new StringBuilder(FIND_LISTVIEW);
		sql.append(orderBy(orderByField));
		sql.append(limit());
		
		return jdbcTemplate.query(sql.toString(), new ContentListViewRowMapper(), GlobalConstants.STATUS.NORMAL, start, offset);
	}
	
	public long findByPicTextIdCount(){
		return jdbcTemplate.queryForLong(FIND_LISTVIEW_COUNT, GlobalConstants.STATUS.NORMAL);
	}
	
	public List<ContentListView> findByTagId(Integer tagId, String orderByField, int start, int offset){
		StringBuilder sql = new StringBuilder(FIND_BY_TAGID);
		sql.append(orderBy(orderByField));
		sql.append(limit());
		
		return jdbcTemplate.query(sql.toString(), new ContentListViewRowMapper(), tagId, GlobalConstants.STATUS.NORMAL, start, offset);
	}
	public long findByTagIdCount(Integer tagId){
		return jdbcTemplate.queryForLong(FIND_BY_TAGID_COUNT, tagId, GlobalConstants.STATUS.NORMAL);
	}
	
}
